package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.dto.TradeTransferDto;
import com.tianqu.tms.www.statistics.dto.TradeTransferPreMonthDto;
import com.tianqu.tms.www.statistics.dto.TradeTransferYesteryearDto;

public class TmsTransferDayCountDao extends TmsDao{

	public TmsTransferDayCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 获得统计开始日期
	 * @return
	 * @throws TmsDaoException
	 */
	public String getStartCountDate() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String firstDate = "";
		
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" * ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" ORDER BY COUNTDATE ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			// 获得第一条的统计日期
			if (rs.next()) {
				// 统计日期
				firstDate = rs.getString("countDate");
			}

			return firstDate;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 取得二、累计交易过户总数 & 三、累计交易过户总价
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getAccumulativeTransaction()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" IFNULL(SUM(TRANSFERNUM), 0) AS TRANSFERNUMTOTAL, IFNULL(SUM(TRANSFERPRICE), 0) AS TRANSFERPRICETOTAL ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				// 累计交易过户总数
				tradeTransferDto.setTransferNumTotal(rs.getBigDecimal("transferNumTotal"));
				// 累计交易过户总价
				tradeTransferDto.setTransferPriceTotal(rs.getBigDecimal("transferPriceTotal"));
			}

			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 取得今天交易过户量 &今天商户交易过户量&今天社会交易过户量
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getTodayTransaction()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" * ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" WHERE COUNTDATE = DATE_FORMAT(NOW(),'%Y-%m-%d') ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				// 今天交易过户量
				tradeTransferDto.setTransferNum(rs.getInt("transferNum"));
				// 今天商户交易过户量
				tradeTransferDto.setTranType11(rs.getInt("tranType11"));
				// 今天社会交易过户量
				tradeTransferDto.setTranType12(rs.getInt("tranType12"));
				//本地过户量
				tradeTransferDto.setTranNum1(rs.getInt("tranNum1"));
				//本地转出量
				tradeTransferDto.setTranNum2(rs.getInt("tranNum2"));
				//外地转入量
				tradeTransferDto.setTranNum3(rs.getInt("tranNum3"));
				//新车上牌量
				tradeTransferDto.setTranNum4(rs.getInt("tranNum4"));
				//县区车辆量
				tradeTransferDto.setTranNum5(rs.getInt("tranNum5"));
				// 变更业务量
				tradeTransferDto.setTranNum6(rs.getInt("tranNum6"));
				//补牌补证补书量
				tradeTransferDto.setTranNum7(rs.getInt("tranNum7"));
			}

			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 当月新增数据 交易过户量（transferNum） 商户业务量（tranType11） 社会业务量（tranType12）
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getMonthIncreaseInfo()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" IFNULL(SUM(TRANSFERNUM), 0) AS TRANSFERNUMMONTHTOTAL, IFNULL(SUM(TRANTYPE11), 0) AS TRANTYPE11MONTHTOTAL, IFNULL(SUM(TRANTYPE12), 0) AS TRANTYPE12MONTHTOTAL, ");
			sql.append(" IFNULL(SUM(tranNum1), 0) as tranNum1MONTHTOTAL, ");//本地过户量
			sql.append(" IFNULL(SUM(tranNum2), 0) as tranNum2MONTHTOTAL, ");//本地转出量
			sql.append(" IFNULL(sum(tranNum3), 0) as tranNum3MONTHTOTAL, ");//外地转入量
			sql.append(" IFNULL(sum(tranNum4), 0) as tranNum4MONTHTOTAL, ");//新车上牌量
			sql.append(" IFNULL(sum(tranNum5), 0) as tranNum5MONTHTOTAL, ");//县区车辆量
			sql.append(" IFNULL(sum(tranNum6), 0) as tranNum6MONTHTOTAL, ");// 变更业务量
			sql.append(" IFNULL(sum(tranNum7), 0) as tranNum7MONTHTOTAL ");//补牌补证补书量
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" WHERE COUNTY = DATE_FORMAT(NOW(),'%Y') ");
			sql.append(" AND COUNTM = DATE_FORMAT(NOW(),'%c') ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				// 当前月份的交易过户量
				tradeTransferDto.setTransferNumMonthTotal(rs.getBigDecimal("transferNumMonthTotal"));
				// 当前月份的交易过户量
				tradeTransferDto.setTranType11MonthTotal(rs.getBigDecimal("tranType11MonthTotal"));
				// 当前月份的社会交易过户量
				tradeTransferDto.setTranType12MonthTotal(rs.getBigDecimal("tranType12MonthTotal"));
				
				// 当前月份的本地过户量
				tradeTransferDto.setTranNum1MonthTotal(rs.getBigDecimal("tranNum1MonthTotal"));
				//当前月份的本地转出量
				tradeTransferDto.setTranNum2MonthTotal(rs.getBigDecimal("tranNum2MonthTotal"));
				//当前月份的外地转入量
				tradeTransferDto.setTranNum3MonthTotal(rs.getBigDecimal("tranNum3MonthTotal"));
				//当前月份的新车上牌量
				tradeTransferDto.setTranNum4MonthTotal(rs.getBigDecimal("tranNum4MonthTotal"));
				//当前月份的县区车辆量
				tradeTransferDto.setTranNum5MonthTotal(rs.getBigDecimal("tranNum5MonthTotal"));
				// 当前月份的变更业务量
				tradeTransferDto.setTranNum6MonthTotal(rs.getBigDecimal("tranNum6MonthTotal"));
				//当前月份的补牌补证补书量
				tradeTransferDto.setTranNum7MonthTotal(rs.getBigDecimal("tranNum7MonthTotal"));
			}

			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 取得上一年度同期累计交易过户数量 &上一年度同期累计商户交易过户量&上一年度同期累计社会交易过户量
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferYesteryearDto getYesteryearTransaction()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferYesteryearDto tradeTransferYesteryearDto = new TradeTransferYesteryearDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" IFNULL(SUM(TRANSFERNUM), 0) AS TRANSFERNUMYMONTHTOTAL, IFNULL(SUM(TRANTYPE11), 0) AS TRANTYPE11YMONTHTOTAL, IFNULL(SUM(TRANTYPE12), 0) AS TRANTYPE12YMONTHTOTAL ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_MONTH_COUNT);
			sql.append(" where countY = date_format(DATE_SUB(NOW(), INTERVAL 1 YEAR), '%Y') ");
			sql.append(" and countM = DATE_FORMAT(NOW(),'%c') ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				// 今天交易过户量
				tradeTransferYesteryearDto.setTransferNumYMonthTotal(rs.getBigDecimal("transferNumYMonthTotal"));
				// 今天商户交易过户量
				tradeTransferYesteryearDto.setTranType11YMonthTotal(rs.getBigDecimal("tranType11YMonthTotal"));
				// 今天社会交易过户量
				tradeTransferYesteryearDto.setTranType12YMonthTotal(rs.getBigDecimal("tranType12YMonthTotal"));
			}

			return tradeTransferYesteryearDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 取得上一月度同期累计交易过户数量 &上一月度同期累计商户交易过户量&上一月度同期累计社会交易过户量
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferPreMonthDto getPreMonthTransaction()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferPreMonthDto tradeTransferPreMonthDto = new TradeTransferPreMonthDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" IFNULL(sum(transferNum), 0) as transferNumPreMonthTotal, IFNULL(sum(tranType11), 0) as tranType11PreMonthTotal, IFNULL(sum(tranType12), 0) as tranType12PreMonthTotal ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" where countY = DATE_FORMAT(NOW(),'%Y')   ");
			sql.append(" and countM = date_format(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%c') ");
			sql.append(" and countDate <= concat(countY,LPAD(countM, 2, 0),DATE_FORMAT(NOW(),'%d')) ");
			sql.append(" group by countY,countM ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				// 今天交易过户量
				tradeTransferPreMonthDto.setTransferNumPreMonthTotal(rs.getBigDecimal("transferNumPreMonthTotal"));
				// 今天商户交易过户量
				tradeTransferPreMonthDto.setTranType11PreMonthTotal(rs.getBigDecimal("tranType11PreMonthTotal"));
				// 今天社会交易过户量
				tradeTransferPreMonthDto.setTranType12PreMonthTotal(rs.getBigDecimal("tranType12PreMonthTotal"));
			}

			return tradeTransferPreMonthDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 八、价格分布 九、车龄分布 十、国产VS进口 十一、个人VS企业十二、黄牌/蓝牌
	 * @return
	 * @throws TmsDaoException
	 */
	public TradeTransferDto getPriceInfo()  throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TradeTransferDto tradeTransferDto = new TradeTransferDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" IFNULL(sum(carPrice1+carPrice2+carPrice3+carPrice4+carPrice5), 0) as under5, ");//5万以下
			sql.append(" IFNULL(sum(carPrice6+carPrice7+carPrice8), 0) as under58, ");//5~8万
			sql.append(" IFNULL(sum(carPrice9+carPrice10), 0) as under810, ");//8~10万
			sql.append(" IFNULL(sum(carPrice11+carPrice12+carPrice13+carPrice14+carPrice15), 0) as under1015, ");//10~15万
			sql.append(" IFNULL(sum(carPrice16+carPrice17+carPrice18+carPrice19+carPrice20), 0) as under1520, ");//15~20万
			sql.append(" IFNULL(sum(carPrice21+carPrice22+carPrice23+carPrice24+carPrice25), 0) as under2025, ");//20~25万
			sql.append(" IFNULL(sum(carPrice26+carPrice27+carPrice28+carPrice29+carPrice30+carPrice31+carPrice32+carPrice33+carPrice34+carPrice35+carPrice36+carPrice37+carPrice38+carPrice39+carPrice40), 0) as under2540, ");// 25~40万
			sql.append(" IFNULL(sum(carPrice41), 0) as up40, ");//40万以上
			sql.append(" IFNULL(sum(carAge1), 0) as carAge1, ");//# 1年以内
			sql.append(" IFNULL(sum(carAge2+carAge3), 0) as carAge13, ");//1~3年
			sql.append(" IFNULL(sum(carAge4+carAge5+carAge6), 0)  as carAge36, ");//3~6年
			sql.append(" IFNULL(sum(carAge7), 0) as carAge7, ");//6年以上
			sql.append(" IFNULL(sum(tranType21), 0) as tranType21Total90, ");//国产业务量
			sql.append(" IFNULL(sum(tranType22), 0) as tranType22Total90, ");//进口业务量
			sql.append(" IFNULL(sum(tranType31), 0) as tranType31Total90, ");//个人业务量
			sql.append(" IFNULL(sum(tranType32), 0) as tranType32Total90, ");//企业业务量
			sql.append(" IFNULL(sum(tranType41), 0) as tranType41Total90, ");//黄牌业务量
			sql.append(" IFNULL(sum(tranType42), 0) as tranType42Total90 ");//蓝牌业务量
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_DAY_COUNT);
			sql.append(" where countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y%m%d') and date_format(CURDATE(),'%Y%m%d') ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			
			if (rs.next()) {
				
				tradeTransferDto.setUnder5(rs.getInt("under5"));
				tradeTransferDto.setUnder58(rs.getInt("under58"));
				tradeTransferDto.setUnder810(rs.getInt("under810"));
				tradeTransferDto.setUnder1015(rs.getInt("under1015"));
				tradeTransferDto.setUnder1520(rs.getInt("under1520"));
				tradeTransferDto.setUnder2025(rs.getInt("under2025"));
				tradeTransferDto.setUnder2540(rs.getInt("under2540"));
				tradeTransferDto.setUp40(rs.getInt("up40"));
				
				tradeTransferDto.setCarAge1(rs.getInt("carAge1"));
				tradeTransferDto.setCarAge13(rs.getInt("carAge13"));
				tradeTransferDto.setCarAge36(rs.getInt("carAge36"));
				tradeTransferDto.setCarAge7(rs.getInt("carAge7"));
				
				tradeTransferDto.setTranType21Total90(rs.getInt("tranType21Total90"));
				tradeTransferDto.setTranType22Total90(rs.getInt("tranType22Total90"));
				tradeTransferDto.setTranType31Total90(rs.getInt("tranType31Total90"));
				tradeTransferDto.setTranType32Total90(rs.getInt("tranType32Total90"));
				tradeTransferDto.setTranType41Total90(rs.getInt("tranType41Total90"));
				tradeTransferDto.setTranType42Total90(rs.getInt("tranType42Total90"));
			}

			return tradeTransferDto;
		} catch (Exception e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}
}
